﻿using System;
using System.Collections.Generic;
using System.Linq;
using ErikEJ.SqlCeScripting;
using SS.Common.DBBuilder.DBDataStructure;
using SS.Common.DBBuilder.DBInteraction;
using Attribute = SS.Common.DBBuilder.DBDataStructure.Attribute;
using ForeignKey = ErikEJ.SqlCeScripting.Constraint;
using PrimaryKey = ErikEJ.SqlCeScripting.PrimaryKey;

namespace SS.Common.DBBuilder
{
    /// <summary>
    /// The class implement AbstractDataContext that is used to create DB structure of SQL Server CE 
    /// </summary>
    public class SQLServerCEDataContext: AbstractDataContext
    {
        #region Ctors

        /// <summary>
        /// Initialize instance of AdminDataContext with datasource and database name as inputs
        /// </summary>
        public SQLServerCEDataContext(string inputDataBaseName)
        {
            this._provider = new SQLServerCEDataProvider(inputDataBaseName);
        }

        #endregion

        #region Help methods

        /// <summary>
        /// Update foreign keys and primary keys for all tables of database
        /// </summary>
        /// <param name="DB"></param>
        protected override void SetKeys(DataBase DB)
        {
            Logger.Info("SetKeys --> Start");

            List<Column> allColumns;
            List<PrimaryKey> allPKs;
            List<ForeignKey> allFKs;

            // Get all Column
            using (var _repository = new DB4Repository(_provider.DataSource))
            {
                allColumns = _repository.GetAllColumns();
                allPKs = _repository.GetAllPrimaryKeys();
                allFKs = _repository.GetAllForeignKeys();
            }

            // Start update keys
            foreach(Table table in DB.Tables)
            {
                Logger.InfoFormat("Set keys for table {0} --> Start", table.Name);

                var columns = allColumns.Where(n => n.TableName.Equals(table.Name, StringComparison.OrdinalIgnoreCase));
                var fks = allFKs.Where(n => n.ConstraintTableName.Equals(table.Name));
                var pks = allPKs.Where(n => n.TableName.Equals(table.Name, StringComparison.OrdinalIgnoreCase));

                foreach(Attribute attr in table.Attributes)
                {
                    var referTo = string.Empty;
                    if (this.IsFK(fks, attr.Name, ref referTo))
                    {
                        attr.IsForeignKey = true;
                        attr.ReferTo = referTo;
                    }
                    else if (this.IsPK(pks, attr.Name))
                    {
                        var col = columns.FirstOrDefault(n => n.ColumnName.Equals(attr.Name, StringComparison.OrdinalIgnoreCase));
                        attr.IsPrimaryKey = true;
                        attr.IsIdentify = col.AutoIncrementBy > 0;
                    }

                    Logger.InfoFormat("Attribute: {0} => IsPrimaryKey: {1}, IsIdentify: {2}, IsForeignKey: {3}, ReferTo: {4}",
                        new object[] { attr.Name, attr.IsPrimaryKey, attr.IsIdentify, attr.IsForeignKey, attr.ReferTo });
                }

                Logger.InfoFormat("Set keys for table {0} <-- End", table.Name);
            }

            Logger.Info("SetKeys <-- End");
        }

        /// <summary>
        /// Checking if attribute is foreign key 
        /// </summary>
        /// <param name="fks">List of foreign keys which received before</param>
        /// <param name="attrName">Attribute to check if it is foreign key</param>
        /// <param name="referTo">Table that foreign key refer to</param>
        /// <returns></returns>
        private bool IsFK(IEnumerable<ForeignKey> fks, string attrName, ref string referTo)
        {
            var result = false;
            foreach (ForeignKey fk in fks.Where(fk => fk.Columns.Any(key => key.Equals(attrName))))
            {
                referTo = fk.UniqueConstraintTableName;
                result = true;
                break;
            }
            return result;
        }

        /// <summary>
        /// Checking if attribute is primary key 
        /// </summary>
        /// <param name="pks">List of primary keys which received before</param>
        /// <param name="attrName">Attribute to check if it is primary key</param>
        /// <returns></returns>
        private bool IsPK(IEnumerable<PrimaryKey> pks, string attrName)
        {
            return pks.Any(pk => pk.ColumnName.Equals(attrName));
        }

        #endregion
    }
}
